package com.pur.zsms.accountmanage.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pur.zsms.accountmanage.dao.IAccountDao;
import com.pur.zsms.accountmanage.po.StoreInAccountDetailedPo;
import com.pur.zsms.accountmanage.po.StoreInAccountPo;
import com.pur.zsms.accountmanage.vo.AccountDayDetailVo;
import com.pur.zsms.accountmanage.vo.AccountDayVo;
import com.pur.zsms.accountmanage.vo.AccountVo;
import com.pur.zsms.accountmanage.vo.StoreInAccountVo;
import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.ordercustommanage.po.OrderCustPo;
import com.pur.zsms.ordercustommanage.vo.OrderSupplierVo;
import com.pur.zsms.pmsnmanage.menu.po.MenuPo;
import com.pur.zsms.pmsnmanage.role.vo.RoleVo;
import com.pur.zsms.pmsnmanage.user.dao.IUserDao;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.pmsnmanage.user.po.UserRolePo;
import com.pur.zsms.pmsnmanage.user.vo.UserVo;
import com.pur.zsms.storemanage.dao.IStoreDao;
import com.pur.zsms.storemanage.po.StoreInDetailedPo;
import com.pur.zsms.storemanage.po.StoreInPo;
import com.pur.zsms.storemanage.po.StoreMaterPo;
import com.pur.zsms.storemanage.po.StoreOutDetailedPo;
import com.pur.zsms.storemanage.po.StoreOutPo;
import com.pur.zsms.storemanage.po.StorePo;
import com.pur.zsms.storemanage.vo.StoreInDetailedVo;
import com.pur.zsms.storemanage.vo.StoreInVo;
import com.pur.zsms.storemanage.vo.StoreMaterVo;
import com.pur.zsms.storemanage.vo.StoreOutDetailedVo;
import com.pur.zsms.storemanage.vo.StoreOutVo;
import com.pur.zsms.storemanage.vo.StoreVo;
import com.pur.zsms.systemsetting.datadictionary.po.CodeType;

@Repository("accountDao")
public class AccountDaoImpl extends GenericDao implements IAccountDao {

	@Override
	public PaginationSupport listAccountDetailed(PageController pageController, AccountVo accountVo)
	        throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT b.id, b.store_in_id, c.store_house_id, c.store_name, c.store_cust_id , d.real_name, a.sup_id, e.sup_companyname, b.mater_id, f.mater_name, b.mater_level, a.store_in_way, a.store_in_state, a.creat_date, b.mater_num, b.mater_value, b.account_state, b.account_date FROM t_store_in a, t_store_in_detailed b, t_store c , t_user d, t_supplier e, t_material f WHERE a.store_in_id= b.store_in_id AND a.cust_id= c.store_cust_id AND c.store_cust_id=d.user_id AND a.sup_id=e.sup_id AND f.mater_id = b.mater_id");
		ql.append(" and a.sup_id='" + accountVo.getSupId() + "' ");
		ql.append(" and b.account_state = 1 ");
		if (accountVo.getCreatDateStart() != null && !accountVo.getCreatDateStart().equals("")) {
			ql.append(" and a.creat_date >= '" + accountVo.getCreatDateStart() + "' ");
		}
		ql.append("and a.creat_date <= '" + accountVo.getCreatDateEnd() + "' ");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport accountListForCust(PageController pageController, AccountDayVo accountDayVo)
	        throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT a1, a2, a3, a4, b1, FORMAT((b1 - a1) * 100 / b1, 2) FROM (SELECT SUM(store_out_value) AS a1, creat_date AS a2, cust_id As a3, real_name AS a4 FROM t_store_out , t_user WHERE 1=1 ");
		if (accountDayVo.getCustId() != 0) {
			ql.append(" AND cust_id = '" + accountDayVo.getCustId() + "'");
		}
		if (accountDayVo.getOrdDateStart() != null && !accountDayVo.getOrdDateStart().equals("")) {
			ql.append(" AND creat_date >= '" + accountDayVo.getOrdDateStart() + "'");
		}
		if (accountDayVo.getOrdDateEnd() != null && !accountDayVo.getOrdDateEnd().equals("")) {
			ql.append(" AND creat_date <= '" + accountDayVo.getOrdDateEnd() + "'");
		}
		ql.append(" AND user_id = cust_id GROUP BY creat_date,cust_id) a LEFT JOIN (SELECT SUM(turnover_value) AS b1, turnover_date AS b2,cust_id AS b3 FROM y_turnover  "
		        + " GROUP BY turnover_date,cust_id) b ON a.a2 = b.b2 and a.a3 = b.b3");

		ql.append(" UNION ");
		ql.append("SELECT a1, b2, b3, b4, b1, FORMAT((b1 - a1) * 100 / b1, 2) FROM (SELECT SUM(turnover_value) AS b1, turnover_date AS b2 , cust_id AS b3, real_name AS b4 FROM y_turnover ,t_user WHERE 1=1 ");
		if (accountDayVo.getCustId() != 0) {
			ql.append(" AND cust_id = '" + accountDayVo.getCustId() + "'");
		}
		if (accountDayVo.getOrdDateStart() != null && !accountDayVo.getOrdDateStart().equals("")) {
			ql.append(" AND turnover_date >= '" + accountDayVo.getOrdDateStart() + "'");
		}
		if (accountDayVo.getOrdDateEnd() != null && !accountDayVo.getOrdDateEnd().equals("")) {
			ql.append(" AND turnover_date <= '" + accountDayVo.getOrdDateEnd() + "'");
		}
		ql.append(" AND cust_id = user_id GROUP BY turnover_date,cust_id) b LEFT JOIN (SELECT SUM(store_out_value) AS a1, creat_date AS a2, cust_id AS a3, real_name AS a4 FROM t_store_out, t_user WHERE "
		        + " user_id = cust_id GROUP BY creat_date,cust_id) a ON a.a2 = b.b2 and a.a3 = b.b3 ");

		ql.append(" ORDER BY  a2 DESC");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport accountListForCustMonth(PageController pageController, AccountDayVo accountDayVo)
	        throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT a1, LEFT(a2,7) as order1, a3, a4, b1, FORMAT((b1 - a1) * 100 / b1, 2) FROM (SELECT SUM(store_out_value) AS a1, creat_date AS a2, cust_id As a3, real_name AS a4 FROM t_store_out , t_user WHERE 1=1 ");
		if (accountDayVo.getCustId() != 0) {
			ql.append(" AND cust_id = '" + accountDayVo.getCustId() + "'");
		}
		if (accountDayVo.getOrdDateMonthStart() != null && !accountDayVo.getOrdDateMonthStart().equals("")) {
			ql.append(" AND LEFT(creat_date,7) >= '" + accountDayVo.getOrdDateMonthStart() + "'");
		}
		if (accountDayVo.getOrdDateMonthEnd() != null && !accountDayVo.getOrdDateMonthEnd().equals("")) {
			ql.append(" AND LEFT(creat_date,7) <= '" + accountDayVo.getOrdDateMonthEnd() + "'");
		}
		ql.append(" AND user_id = cust_id GROUP BY LEFT(creat_date,7),cust_id) a LEFT JOIN (SELECT SUM(turnover_value) AS b1, turnover_date AS b2,cust_id AS b3 FROM y_turnover  "
		        + " GROUP BY LEFT(turnover_date,7),cust_id) b ON a.a2 = b.b2 and a.a3 = b.b3");

		ql.append(" UNION ");
		ql.append("SELECT a1, LEFT(b2,7) as order1, b3, b4, b1, FORMAT((b1 - a1) * 100 / b1, 2) FROM (SELECT SUM(turnover_value) AS b1, turnover_date AS b2 , cust_id AS b3, real_name AS b4 FROM y_turnover ,t_user WHERE 1=1 ");
		if (accountDayVo.getCustId() != 0) {
			ql.append(" AND cust_id = '" + accountDayVo.getCustId() + "'");
		}
		if (accountDayVo.getOrdDateMonthStart() != null && !accountDayVo.getOrdDateMonthStart().equals("")) {
			ql.append(" AND LEFT(turnover_date,7) >= '" + accountDayVo.getOrdDateMonthStart() + "'");
		}
		if (accountDayVo.getOrdDateMonthEnd() != null && !accountDayVo.getOrdDateMonthEnd().equals("")) {
			ql.append(" AND LEFT(turnover_date,7) <= '" + accountDayVo.getOrdDateMonthEnd() + "'");
		}
		ql.append(" AND cust_id = user_id GROUP BY LEFT(turnover_date,7),cust_id) b LEFT JOIN (SELECT SUM(store_out_value) AS a1, creat_date AS a2, cust_id AS a3, real_name AS a4 FROM t_store_out, t_user WHERE "
		        + " user_id = cust_id GROUP BY LEFT(creat_date,7),cust_id) a ON a.a2 = b.b2 and a.a3 = b.b3 ");

		ql.append(" ORDER BY  order1 DESC");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	@Override
	public PaginationSupport listAccountDetailedSelect(PageController pageController, AccountVo accountVo)
	        throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT b.id, b.store_in_id, c.store_house_id, c.store_name, c.store_cust_id, d.real_name, a.sup_id, e.sup_companyname, b.mater_id, f.mater_name, b.mater_level, a.store_in_way, a.store_in_state, a.creat_date, b.mater_num, b.mater_value, b.account_state, b.account_date FROM t_store_in a, t_store_in_account n, t_store_in_account_detailed m, t_store c, t_store_in_detailed b, t_user d , t_supplier e, t_material f WHERE a.store_in_id = b.store_in_id AND n.account_id = m.account_id AND m.id = b.id AND a.cust_id = c.store_cust_id AND c.store_cust_id = d.user_id AND a.sup_id = e.sup_id AND f.mater_id = b.mater_id AND n.account_id = '"
		        + accountVo.getAccountId() + "' ");
		/*
		 * ql.append(" and a.sup_id='" + accountVo.getSupId() + "' ");
		 * ql.append(" and b.account_state = 1 "); if
		 * (accountVo.getCreatDateStart() != null &&
		 * !accountVo.getCreatDateStart().equals("")) {
		 * ql.append(" and a.creat_date >= '" + accountVo.getCreatDateStart() +
		 * "' "); } ql.append("and a.creat_date <= '" +
		 * accountVo.getCreatDateEnd() + "' ");
		 */
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	@Override
	public PaginationSupport listAccountProcess(PageController pageController,
	        StoreInAccountVo storeInAccountVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT DISTINCT a.account_id, a.account_date, a.mater_value_all, a.remark, d.sup_id, e.sup_companyname FROM t_store_in_account a LEFT JOIN t_store_in_account_detailed b ON a.account_id = b.account_id LEFT JOIN t_store_in_detailed c ON b.id = c.id LEFT JOIN t_store_in d ON c.store_in_id = d.store_in_id LEFT JOIN t_supplier e ON e.sup_id = d.sup_id ");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public int addStoreInAccountPo(StoreInAccountPo po) throws GenericException {
		this.merge(po);
		List quert = this.entityManager.createNativeQuery("SELECT LAST_INSERT_ID()").getResultList();
		return Integer.valueOf(quert.get(0).toString());
	}

	public void addStoreInAccountDetailedPo(StoreInAccountDetailedPo po) throws GenericException {
		this.merge(po);
	}

	public PaginationSupport accountListForCustDetailForOrder(PageController pageController,
	        AccountDayDetailVo accountDayDetailVo) throws GenericException {
		StringBuffer ql = new StringBuffer();

		ql.append(" SELECT a.ord_id ,b.ord_cust_id ,u.real_name ,b.ord_date ,a.ord_mater_id ,m.mater_name ,m.specifications ,m.brands ,a.ord_mater_weight ,m.unit ,a.ord_mater_value ,a.ord_sup_id ,s.sup_companyname,a.return_goods FROM t_order_supplier_mater_reality a, t_order_custom b, t_user u, t_material m, t_supplier s WHERE a. ord_id = b. ord_id AND b. ord_cust_id = u. user_id AND m. mater_id = a. ord_mater_id AND s. sup_id = a. ord_sup_id");
		ql.append(" AND b. ord_cust_id = '" + accountDayDetailVo.getCustId() + "'");
		ql.append(" AND ord_date = '" + accountDayDetailVo.getOrdDate() + "'");
		ql.append(" ORDER BY b.ord_cust_id,b. ord_date ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport accountListForCustDetailForStoreOut(PageController pageController,
	        AccountDayDetailVo accountDayDetailVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT a.mater_id, FORMAT(a.mater_num,2), FORMAT(a.mater_value,2), FORMAT(a.mater_num*a.mater_value,2), b.remark,c.`mater_name`,a.`store_out_id` FROM t_store_out_detailed_value a, t_store_out b,t_material c WHERE a.store_out_id = b.store_out_id AND c.`mater_id` = a.`mater_id` ");
		ql.append(" AND b.creat_date = '" + accountDayDetailVo.getOrdDate() + "' ");
		ql.append(" AND b.cust_id = '" + accountDayDetailVo.getCustId() + "' ");
		ql.append(" AND b.store_out_state = 2 ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findCaiwuCheck(PageController pageController, OrderSupplierVo orderSupplierVo)
	        throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT o.oid, o.ord_id, o.ord_cust_id, o.ord_sup_id, o.ord_state, o.ord_date , o.ord_day, o.update_time, o.insert_by, o.ord_value, b.real_name, c.sup_companyname,o.ord_payment_state,o.ord_value_real,o.commit_deliver_state,o.`ord_value_real_cost`,FORMAT((o.`ord_value_real`-o.ord_value_real_cost)*100/o.`ord_value_real`,2),o.`ord_value_real`-o.ord_value_real_cost FROM t_order_supplier o ,t_user b,t_supplier c WHERE o.ord_cust_id = b.user_id AND o.ord_sup_id = c.sup_id ");

		if (orderSupplierVo.getOrdDate() != null && !orderSupplierVo.getOrdDate().equals("")) {
			ql.append(" and o.ord_date  = '");
			ql.append(orderSupplierVo.getOrdDate());
			ql.append("'");
		}
		if (orderSupplierVo.getOrdState() != 0) {
			ql.append(" and o.ord_state  = '");
			ql.append(orderSupplierVo.getOrdState());
			ql.append("'");
		}
		if (orderSupplierVo.getOrdCustName() != null && !orderSupplierVo.getOrdCustName().equals("")) {
			ql.append(" and b.real_name like '%");
			ql.append(orderSupplierVo.getOrdCustName());
			ql.append("%'");
		}

		if (orderSupplierVo.getOrdSupId() != 0) {
			ql.append(" and o.ord_sup_id  = '");
			ql.append(orderSupplierVo.getOrdSupId());
			ql.append("'");
		}

		ql.append(" AND o.ord_date >='" + orderSupplierVo.getOrdDateStart() + "' AND o.ord_date <='"
		        + orderSupplierVo.getOrdDateEnd() + "'");

		if (orderSupplierVo.getCustIdAll() != null && !orderSupplierVo.getCustIdAll().equals("")) {
			ql.append("AND o.ord_cust_id IN(" + orderSupplierVo.getCustIdAll() + ")");

		}
		ql.append(" ORDER BY  o.ord_id");

		return this.findByNativeQuery(ql.toString(), pageController);

	}
}
